import mysql.connector
import pandas as pd
from sqlalchemy import create_engine

# 创建SQLAlchemy的MySQL连接
def create_db_connection():
    # 使用PyMySQL作为MySQL的连接器
    engine = create_engine("mysql+pymysql://root:123456@localhost/5i40?charset=utf8mb4")
    return engine

# 执行查询并返回DataFrame
def query_to_dataframe(query):
    engine = create_db_connection()
    query_result = pd.read_sql(query, con=engine)
    return query_result

# 导出到Excel
def export_to_excel():
    # 查询语句：使用JOIN将三个表关联起来
    query = """
    SELECT 
        g.id AS 商品id,
        g.category_id AS 类别ID,
        g.subtitle AS 子标题,
        g.attribute_ids AS 属性值ids,
        g.brand_id AS 品牌ID,
        g.goods_sn AS 货号,
        g.title AS 商品标题,
        g.keywords AS 关键字,
        g.description AS 描述,
        g.marketprice AS 市场售价,
        g.price AS 商城售价,
        g.stocks AS 库存,
        g.sales AS 销量,
        g.guarantee_ids AS 服务保障,
        g.star AS 星级,
        g.views AS 浏览次数,
        g.comments AS 评论数,
        g.shares AS 分享次数,
        g.image AS 缩略图,
        g.images AS 预览图,
        g.content AS 商品内容详情,
        g.corner AS 角标文字,
        g.flag AS 标志,
        g.spectype AS 规格类型,
        g.weight AS 重量,
        g.isvirtual AS 虚拟物品,
        g.freight_id AS 运费模板ID,
        g.weigh AS 排序,
        g.createtime AS 创建时间,
        g.updatetime AS 更新时间,
        g.deletetime AS 删除时间,
        g.status AS 状态,
        s.sku_id AS SKU,
        s.goods_sn AS SKU货号,
        s.image AS 规格封面,
        s.price AS SKU价格,
        s.marketprice AS SKU市场价,
        s.stocks AS SKU库存数量,
        s.sales AS SKU销量,
        s.weigh AS SKU权重,
        ss.spec_id AS 规格ID,
        ss.spec_value_id AS 规格值ID
    FROM 
        fa_shop_goods g
    LEFT JOIN 
        fa_shop_goods_sku s ON g.id = s.goods_id
    LEFT JOIN 
        fa_shop_goods_sku_spec ss ON g.id = ss.goods_id AND s.sku_id = ss.spec_id
    """

    # 获取所有关联后的数据
    df = query_to_dataframe(query)

    # 创建Excel writer，并将数据写入单一Sheet
    with pd.ExcelWriter('fa_shop_combined_data.xlsx', engine='openpyxl') as writer:
        df.to_excel(writer, sheet_name='商品信息', index=False)

    print("Excel导出成功！")

if __name__ == '__main__':
    export_to_excel()
